﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.IO;
using System.Text;
using System.Windows.Forms;
using UCL.Forms;
using UCL.Data;
using UCL;

namespace DBKit5
{
    public partial class frmSqlGenerateForExcel : Form
    {
        public frmSqlGenerateForExcel()
        {
            InitializeComponent();
            dgvExcelData.SetRowHeaderIndex();
        }

        private void frmSqlGenerateForExcel_Load(object sender, EventArgs e)
        {
            ButtonClickEventConfig();
            MenuItemClickEventConfig();
        }

        // 按钮点击事件配置
        private void ButtonClickEventConfig()
        {
            btnBrowserExcel.Click += new EventHandler(btnBrowserExcel_Click);
            btnRun.Click += new EventHandler(btnRun_Click);
            lnkReadExcel.Click += new EventHandler(lnkReadExcel_Click);
        }

        // 菜单点击事件配置
        private void MenuItemClickEventConfig()
        {
            cmist导出ToolStripMenuItem.Click += new EventHandler(cmistMenuItem_Click);
            cmist导入ToolStripMenuItem.Click += new EventHandler(cmistMenuItem_Click);
            cmist复制ToolStripMenuItem.Click += new EventHandler(cmistMenuItem_Click);
            cmist剪切ToolStripMenuItem.Click += new EventHandler(cmistMenuItem_Click);
            cmist清空ToolStripMenuItem.Click += new EventHandler(cmistMenuItem_Click);
            cmist全选ToolStripMenuItem.Click += new EventHandler(cmistMenuItem_Click);
            cmist用记事本打开ToolStripMenuItem.Click += new EventHandler(cmistMenuItem_Click);
            cmist粘帖ToolStripMenuItem.Click += new EventHandler(cmistMenuItem_Click);
        }
        private void cmistMenuItem_Click(object sender, EventArgs e)
        {
            ToolStripMenuItem mi = (ToolStripMenuItem)sender;
            switch (mi.Name)
            {
                case "cmist导出ToolStripMenuItem":
                    this.ShowSaveFileDialog("文本文件 (*.txt)|*.txt", (fn) =>
                    {
                        File.WriteAllText(fn, tbSqlText.Text);
                        return "success";
                    }, "导出成功,是否打开保存目录?");
                    break;
                case "cmist导入ToolStripMenuItem":
                    this.ShowOpenFileDialog("文本文件 (*.txt)|*.txt", (fn) => { tbSqlText.Text = File.ReadAllText(fn); });
                    break;
                case "cmist复制ToolStripMenuItem":
                    tbSqlText.Copy();
                    break;
                case "cmist剪切ToolStripMenuItem":
                    tbSqlText.Cut();
                    break;
                case "cmist清空ToolStripMenuItem":
                    tbSqlText.Clear();
                    break;
                case "cmist全选ToolStripMenuItem":
                    tbSqlText.SelectAll();
                    break;
                case "cmist用记事本打开ToolStripMenuItem":
                    string tmpTxtPath = string.Format("{0}\\tmpWSSql_{1}.txt", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), DateTime.Now.ToString("yyyyMMddHHmmss"));
                    File.WriteAllText(tmpTxtPath, tbSqlText.Text);
                    System.Diagnostics.Process.Start("Notepad", tmpTxtPath);
                    break;
                case "cmist粘帖ToolStripMenuItem":
                    tbSqlText.Paste();
                    break;
            }
        }

        private void btnBrowserExcel_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel 2007文件(*.xls)|*.xls", FileName = tbExcelPath.Text };
            if (DialogResult.OK != ofd.ShowDialog()) return;
            tbExcelPath.Text = ofd.FileName;
        }
        private void lnkReadExcel_Click(object sender, EventArgs e)
        {
            if (tbExcelPath.TextLength == 0) return;
            ExcelHelper eh = new ExcelHelper(tbExcelPath.Text);

            DataTable dt = eh.ExecuteDataTable("select * from [Sheet1$]", chkFirstRowTitle.Checked);
            int rowStart = tbRowStart.Text.ToInt();
            if (rowStart > 0)
            {
                if (dt.Rows.Count > rowStart)
                {
                    while (rowStart > 0)
                    {
                        dt.Rows.RemoveAt(0);
                        rowStart--;
                    }
                }
            }
            dgvExcelData.DataSource = null;
            dgvExcelData.DataSource = dt;
            lblExcelDataInfo.Text = string.Format("共 {0} 条记录", dt.Rows.Count);
        }

        private void btnRun_Click(object sender, EventArgs e)
        {
            tbSqlText.Clear();
            if (tbSqlTpl.TextLength == 0 || dgvExcelData.Rows.Count == 0) return;
            DataTable dt = (DataTable)dgvExcelData.DataSource;
            foreach (DataRow r in dt.Rows)
            {
                tbSqlText.AppendText(string.Format(tbSqlTpl.Text, r.ItemArray));
            }
        }
    }
}
